package com.bungarus.group.jdbc;

import com.bungarus.group.model.Group;
import com.bungarus.group.model.GroupMember;
import com.bungarus.group.model.Member;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;


/**
 * Created by tang on 2018/12/2.
 */
@Component
public class GroupMemberManager {
    Logger logger = LoggerFactory.getLogger(GroupMemberManager.class);

    private static final String DEFAULT_SORT_PROPERTY = "mname";
    private static final int INSERT_BATCH_SIZE = 50;
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public GroupMemberManager(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Transactional(readOnly = true)
    public List<Long> findGroupIdsMemberBelongsTo(GroupMember member) {
        String query = "select gid from t_member_group where mid = ? and tenantId = ? order by gid";
        List<Long> result = jdbcTemplate.query(query, new Object[]{member.getMid(), member.getTenantId()},
                (resultSet, rowNum) -> {
                    return resultSet.getLong("gid");
                });
        return result;
    }

    @Transactional
    public void deleteByGid(GroupMember group) {
        String[] delSqls = {"delete from t_group_member where gid=? and tenantId=?",
                        "delete from t_member_group where gid=? and tenantId=?"};
        for(String delSql: delSqls) {
            jdbcTemplate.execute(delSql, (PreparedStatement preparedStatement) -> {
                preparedStatement.setLong(1, group.getGid());
                preparedStatement.setLong(2, group.getTenantId());
                return preparedStatement.execute();
            });
        }
    }

    @Transactional
    public GroupMember save(GroupMember gm) {
        String[] insertSqls = {"insert into t_group_member (gid, mid, gname, mname, tenantId) values (?,?,?,?,?)",
                        "insert into t_member_group (gid, mid, gname, mname, tenantId) values (?,?,?,?,?)"};
        try {
            for(String insertSql: insertSqls) {
                jdbcTemplate.execute(insertSql, (PreparedStatement preparedStatement) -> {
                    preparedStatement.setLong(1, gm.getGid());
                    preparedStatement.setLong(2, gm.getMid());
                    preparedStatement.setString(3, gm.getgName());
                    preparedStatement.setString(4, gm.getmName());
                    preparedStatement.setLong(5, gm.getTenantId());

                    return preparedStatement.execute();
                });
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return gm;
    }

    @Transactional
    public void saveAll(List<GroupMember> gms) {
        String[] insertSqls = {"insert into t_group_member (gid, mid, gname, mname, tenantId) values (?,?,?,?,?)",
                        "insert into t_member_group (gid, mid, gname, mname, tenantId) values (?,?,?,?,?)"};
        for(String insertSql: insertSqls) {
            for (int i = 0; i < gms.size(); i += INSERT_BATCH_SIZE) {
                final List<GroupMember> batchList = gms.subList(i, i + INSERT_BATCH_SIZE > gms.size() ? gms.size() : INSERT_BATCH_SIZE + i);
                jdbcTemplate.batchUpdate(insertSql, new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                        GroupMember gm = batchList.get(i);
                        preparedStatement.setLong(1, gm.getGid());
                        preparedStatement.setLong(2, gm.getMid());
                        preparedStatement.setString(3, gm.getgName());
                        preparedStatement.setString(4, gm.getmName());
                        preparedStatement.setLong(5, gm.getTenantId());
                    }

                    @Override
                    public int getBatchSize() {
                        return batchList.size();
                    }
                });
            }
        }
    }

    @Transactional(readOnly = true)
    public int totalCount(long tenantId) {
        String querySql = "select count(1) as size from t_group_member where tenantId=?";

        return jdbcTemplate.queryForObject(querySql, new Object[]{tenantId}, (resultSet, i) -> {
            return resultSet.getInt("size");
        });
    }

    @Transactional(readOnly = true)
    public int totalMemberCountOfGroup(GroupMember gm) {
        String querySql = "select count(1) as size from t_group_member where gid=? and tenantId=?";

        return jdbcTemplate.queryForObject(querySql, new Object[]{gm.getGid(), gm.getTenantId()}, (resultSet, i) -> {
            return resultSet.getInt("size");
        });
    }

    @Transactional(readOnly = true)
    public List<Member> findMembers(GroupMember gm, Pageable page) {
        StringBuilder sb =
                new StringBuilder("select mid, mname, tenantId from t_group_member where gid=? and tenantId=?");
        if(null != page) {
            if(page.getSort().isSorted()) {
                sb.append(" order by ");
                sb.append(StringUtils.replace(page.getSort().toString(), ":", ""));
            }
            sb.append(" limit ");
            sb.append(page.getOffset());
            sb.append(", ");
            sb.append(page.getPageSize());
        }
        String querySql = sb.toString();
        return jdbcTemplate.query(querySql, new Object[]{gm.getGid(), gm.getTenantId()},
                (resultSet, rowNum) -> {
                    Member member = new Member();
                    member.set_id(resultSet.getLong("mid"));
                    member.setName(resultSet.getString("mname"));
                    member.setTenantId(resultSet.getLong("tenantId"));
                    return member;
                });
    }

    @Transactional(readOnly = true)
    public List<Member> findMembers(GroupMember gm, int offset, int size) {
        Pageable page = PageRequest.of(offset, size, Sort.by(DEFAULT_SORT_PROPERTY));
        return findMembers(gm, page);
    }

    @Transactional
    public void deleteMemberOfTenant(GroupMember member) {
        String[] delSqls = {"delete t_group_member where mid=? and tenantId=?",
                "delete t_member_group where mid=? and tenantId=?"};
        for(String delSql: delSqls) {
            jdbcTemplate.execute(delSql, (PreparedStatement preparedStatement) -> {
                preparedStatement.setLong(1, member.getMid());
                preparedStatement.setLong(2, member.getTenantId());
                return preparedStatement.execute();
            });
        }
    }

    @Transactional
    public void deleteMemberInGroup(GroupMember gm) {
        String[] delSqls = {"delete t_group_member where mid=? and gid=? and tenantId=?",
                "delete t_member_group where mid=? and gid=? and tenantId=?"};
        for(String delSql: delSqls) {
            jdbcTemplate.execute(delSql, (PreparedStatement preparedStatement) -> {
                preparedStatement.setLong(1, gm.getMid());
                preparedStatement.setLong(2, gm.getGid());
                preparedStatement.setLong(3, gm.getTenantId());
                return preparedStatement.execute();
            });
        }
    }

    @Transactional(readOnly = true)
    public List<Group> findGroupsMemberBelongsTo(GroupMember member) {
        return findGroupsMemberBelongsTo(member, Sort.by("gid"));
    }

    @Transactional(readOnly = true)
    public List<Group> findGroupsMemberBelongsTo(GroupMember member, Sort sort) {
        if(null == sort) {
            throw new IllegalArgumentException("Sort cannot be null.");
        }
        StringBuilder sb =
                new StringBuilder("select gid, gname from t_member_group where mid = ? and tenantId = ?");

        if(sort.isSorted()) {
            sb.append(" order by ");
            sb.append(StringUtils.replace(sort.toString(), ":", ""));
        }

        String querySql = sb.toString();

        List<Group> results = jdbcTemplate.query(querySql, new Object[]{member.getMid(), member.getTenantId()},
                (resultSet, rowNum) -> {
                    Group group = Group.newBuilder()
                            .id(resultSet.getLong("gid"))
                            .name(resultSet.getString("gname"))
                            .tenant(member.getTenantId())
                            .build();
                    return group;
                });
        return results;
    }

    @Transactional(readOnly = true)
    public List<Group> findGroupsMemberBelongsTo(GroupMember member, Pageable page) {
        if(null == page) {
            throw new IllegalArgumentException("page cannot be null.");
        }
        StringBuilder sb =
                new StringBuilder("select gid, gname from t_member_group where mid = ? and tenantId = ?");

        if(page.getSort().isSorted()) {
            sb.append(" order by ");
            sb.append(StringUtils.replace(page.getSort().toString(), ":", ""));
        }
        sb.append(" limit ");
        sb.append(page.getOffset());
        sb.append(", ");
        sb.append(page.getPageSize());

        String querySql = sb.toString();

        List<Group> results = jdbcTemplate.query(querySql, new Object[]{member.getMid(), member.getTenantId()},
                (resultSet, rowNum) -> {
                    Group group = Group.newBuilder()
                            .id(resultSet.getLong("gid"))
                            .name(resultSet.getString("gname"))
                            .tenant(member.getTenantId())
                            .build();
                    return group;
                });
        return results;
    }

    @Transactional(readOnly = true)
    public int totalCountOfGroupsMemberBelongsTo(long mid, long tid) {
        String querySql = "select count(1) as size from t_member_group where mid=? and tenantId=?";

        return jdbcTemplate.queryForObject(querySql, new Object[]{mid, tid}, (resultSet, i) -> {
            return resultSet.getInt("size");
        });
    }
}
